The present disclosure relates to computer databases, and more specifically, to index merge ordering to combine processing of mismatched filtering and ordering criteria into a single operation.
Applications may display data to users that is ordered according to some criteria, such as by dates, alphabetical order, or numerical values. In many cases, the data is a list of results retrieved by executing database queries. Traditionally, to return such results, programmers have either sorted the returned column data, or used an index keyed over the ordering column(s). When there are a large number of rows in the result set, an index is generally preferred as it provides a way to show the initial set of data to the user without having to sort the entire answer set. However, when the query includes unrelated search criteria (for example in an SQL WHERE clause) that will reject a lot of records from going into the result set, the index approach degrades quickly, as potentially a large number of entries in the index may be examined, and rejected, prior to producing enough selected data to display to the user.
A sort needs to process all rows in the result set. Thus, a sort has traditionally been used in cases where there are a relatively small number of rows in the result set. When the result set is large, however, all rows must be processed and sorted, even though the user may only be interested in the first few ordered rows. The processing performance in this regard may therefore be wasteful and undesirable.